Goal:

Use pivot_wider() on a dataset and return zeros instead of NA where values are missing.

# create dataframe
df <- data.frame(group = c("A", "B", "B", "A", "B", "A", "B", "B", "A", "B", "B", "A"),
                 year = c(2000, 2000, 2000, 2001, 2002, 2002, 2002, 2002, 2002, 2003, 2003, 2004),
                 count = c(2, 4, 1, 3, 2, 1, 5, 2, 3, 1, 2, 4))

#summarise by group and year
df_summ <- df %>%
  group_by(group, year) %>%
  summarise(total = sum(count))

What I was doing:

# pivot by year column
df_wide <- df_summ %>%
  pivot_wider(id_cols = group, names_from = "year", values_from = "total")

# replace NAs with zeros in whole data frame
df_wide[is.na(df_wide)] <- 0

What I’m doing now:

# pivot and replace NAs with values_fill option
df_wide_zero <- df_summ %>%
  pivot_wider(id_cols = group, names_from = "year", values_from = "total", values_fill = 0)